-- DROP TABLE EmployeeLocation

-- Create Table, using the new UDT

CREATE TABLE EmployeeLocation (
  Employee int,
  Latitude DMS,
  Longitude DMS)
GO

-- Add some data, which calls Parse() method

INSERT EmployeeLocation VALUES (1,'142.15.27','30.15.9')
INSERT EmployeeLocation VALUES (2,'142.10.13','18.22.14')

-- Reference UDT properties directly

SELECT Employee,
       Latitude.Degrees as [Degrees],
       Latitude.Minutes as Minutes,
       Latitude.Seconds as Seconds
  FROM EmployeeLocation

-- Select Latitude column (binary)

SELECT Employee, Latitude
  FROM EmployeeLocation

-- Select all columns (binary)

SELECT * FROM EmployeeLocation

-- Call ToString() method

SELECT Employee,
       Latitude.ToString() as [Latitude]
  FROM EmployeeLocation

-- Update UDT properties directly

UPDATE EmployeeLocation
   SET Latitude.Degrees = 42
 WHERE Employee = 1

SELECT Employee,
       Latitude.ToString() as [Latitude]
  FROM EmployeeLocation

-- Can't Update more than one UDT property at a time

UPDATE EmployeeLocation
   SET Latitude.Degrees = 42, Latitude.Minutes = 42
 WHERE Employee = 1

-- Support for NULL

UPDATE EmployeeLocation 
   SET Latitude = NULL
 WHERE Employee = 1

INSERT EmployeeLocation VALUES (3,NULL,'35.9.42')

SELECT Employee,
       Latitude.ToString() as [Latitude]
  FROM EmployeeLocation

SELECT Employee,
       Latitude.ToString() as [Latitude]
  FROM EmployeeLocation
 WHERE Latitude IS NULL

-- Try dropping assembly (remember the WITH UNCHECKED DATA option and DBCC CHECKTABLE)

DROP ASSEMBLY CoolDBObjects

-- Drop Type

DROP TYPE DMS
DROP ASSEMBLY CoolDBObjects

-- Done

DROP TABLE EmployeeLocation

-----------------------------------------------------
-- Custom Serialization

CREATE TABLE EmployeeLocation (
  Employee int,
  Latitude DMS,
  Longitude DMS)
GO

-- Add some data, which calls Parse() method

INSERT EmployeeLocation VALUES (1,'142.15.27','30.15.9')

SELECT * FROM EmployeeLocation


SELECT Employee,
       Latitude.ToString() as [Latitude]
  FROM EmployeeLocation

DROP TABLE EmployeeLocation
